package ups.Database;


import java.util.Calendar;
import java.util.Date;
import java.util.ArrayList;
import java.util.Hashtable;

import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

import ups.MapParse.Point;

import ups.Common.Ship;

import java.text.ParseException;
import java.text.ParsePosition;

public class ShipDatabase extends Database {
	
	private ArrayList<String> _shipIDMap;
	
	public ShipDatabase( String hostName, int portNum, String databaseName, String username, String password ) {
		super( hostName, portNum, databaseName, username, password );
		
		/* Need to load the ships into an array */
		_shipIDMap = this.getShipNames();
	}
	
	private String SHIP_TABLE = "ships";
	private String LOCATION_TABLE = "ship_locations";
	
	private String SHIP_NAME_KEY = "name";
	private String SHIP_ID_KEY = "ship_id";
	
	private String LOCATION_DATE_FIELD = "time";
	
	/* Install */
	/* Bad form to write your SQL By Hand Like This */
	public boolean createShipTables() {
		try {
			Statement shipTableStatement = this.getConnection().createStatement();
			shipTableStatement.execute( "CREATE TABLE " + SHIP_TABLE + 
										" ( 'ship_id' INT NOT NULL AUTO_INCREMENT," +
										"'name' TEXT," +
										"PRIMARY KEY('ship_id'))");
			
			Statement locationTableStatement = this.getConnection().createStatement();
			locationTableStatement.execute("CREATE TABLE " + LOCATION_TABLE +	
										" ('location_id' INT NOT NULL AUTO_INCREMENT,"+
										"'ship_id' INT,"+
										"'x' INT,"+
										"'y' INT,"+
										"'time' DATETIME,"+
										"'status' ENUM('stopped', 'moving'),"+
										"'heading' INT,"+
										"PRIMARY KEY('location_id'))");
			
			return true;
		} catch (SQLException e ) {
			return false;
		}
	}
	
	/* Returns a list of ship names, indecies into the list are their "ship_id" */
	public ArrayList<String> getShipNames() {
		try {
			ResultSet rs = super.get( SHIP_TABLE );
			ArrayList<String> result = new ArrayList<String>();
			result.add( "" );
			
			while( rs.next() ) {
				result.add( rs.getString( SHIP_NAME_KEY ));
			}
			
			int idx = 1;
			for( String s : result ) {
				//System.out.println( idx + ": " + s );
				idx++;
			}
			return result;
		} catch ( SQLException e ) {
			e.printStackTrace();
			return new ArrayList<String>();
		}
		
	}
	/* Sets the ship names */
	public void setShipNames(ArrayList<String> names) {
		if( this.getShipNames().size() != 0 ) {
			System.err.println( "Error: Ship Names Already Entered");
		} else {
			Hashtable<String,String> nameHashtable = new Hashtable<String,String>();
			for( String name : names ) {
				nameHashtable.put( SHIP_NAME_KEY, name );
				super.put( SHIP_TABLE , nameHashtable );
			}
		}
		
		_shipIDMap = getShipNames();
	}
		
	/* Gets the location entries */
	public ResultSet getShipLocations() {
		return super.get( LOCATION_TABLE );
	}
	
	public ArrayList<Ship> getShipsForDay( Date day ) {
		ArrayList<Ship> shipLocations = new ArrayList<Ship>();
		ResultSet rs =  super.getFromDay( LOCATION_TABLE, day, LOCATION_DATE_FIELD );
		try {
			while( rs.next() ) {
				boolean isMoving = rs.getString("status").equals("moving");
			
				Ship s = new Ship( 	_shipIDMap.get( rs.getInt( "ship_id" )),
								   	new Point( rs.getInt("x"), rs.getInt("y")),
									isMoving,
									new Integer( rs.getString("heading")).intValue() );
				s.setDate( super.getDateTimeFormat().parse( rs.getString("time"), new ParsePosition(0)));
				shipLocations.add( s );
			}
		} catch( SQLException e ) {
			e.printStackTrace();
		}	
		return shipLocations;
	}
	
	public ArrayList<Ship> getShipsForDay( String shipName, Date day ) {
		ArrayList<Ship> locations = new ArrayList<Ship>();
		
		try {
			Statement stmt = this.getConnection().createStatement();
			Calendar cal = Calendar.getInstance();
			cal.setTime( day );
			cal.set( Calendar.HOUR , 4 );
			Date firstDay = cal.getTime();
			cal.add( Calendar.DATE, 1 );
			Date nextDay = cal.getTime();
			String query =  "SELECT * from ship_locations, ships " +
							"WHERE ships.ship_id = ship_locations.ship_id AND ships.name = \"" + shipName + "\" " +
							"AND ship_locations.time BETWEEN \"" + getDateTimeFormat().format( firstDay ) + "\" AND \"" + getDateTimeFormat().format( nextDay ) + "\" ORDER BY ship_locations.time;";
			System.out.println( query );
			ResultSet rs = stmt.executeQuery( query );
			while( rs.next() ) {
				boolean isMoving;
				if( rs.getString("ship_locations.status").equals("stopped")) {
					isMoving = false;
				} else {
					isMoving = true;
				}
				Ship ship = new Ship( rs.getString("ships.name"), new Point( rs.getInt("ship_locations.x"), rs.getInt( "ship_locations.y")), isMoving, 0 );
				//System.out.println( rs.getString("ship_locations.time") );
				ship.setDate( getDateTimeFormat().parse( rs.getString("ship_locations.time")));
				//System.out.println( ship.toString() );
				locations.add( ship );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch ( ParseException pex) {
			pex.printStackTrace();
		}
		return locations;
	}
	
	public ArrayList<Ship> getShips() {
		ArrayList<Ship> shipLocations = new ArrayList<Ship>();
		
		
		ResultSet rs = getShipLocations();
		try {
			while( rs.next() ) {
				boolean isMoving = rs.getString("status").equals("moving");
			
				Ship s = new Ship( 	_shipIDMap.get( rs.getInt( "ship_id" )),
								   	new Point( rs.getInt("x"), rs.getInt("y")),
									isMoving,
									new Integer( rs.getString("heading")).intValue() );
				s.setDate( super.getDateTimeFormat().parse( rs.getString("time"), new ParsePosition(0)));
				shipLocations.add( s );
			}
		} catch( SQLException e ) {
			e.printStackTrace();
		}
		
		
		return shipLocations;
	}
	
	public void printShipLocations() {
		try {
		ResultSet rs = this.getShipLocations();
		
		while( rs.next() ) {
			System.out.println( rs.getInt("location_id") + ": " +
			 					_shipIDMap.get( rs.getInt( "ship_id" )) + " " + 
								"(" + rs.getInt( "x" ) + ", " + rs.getInt( "y" ) + ") " +
							 	" " + rs.getString("time") + " " + rs.getString("status") + " " + rs.getString( "heading"));
		}
		} catch (SQLException e ) {
			e.printStackTrace();
		}
		
	}
	
	/* Inserts a location entry */
	public void putShipLocation( Ship ship, Date date ) {
		try {
		Hashtable<String,String> values = new Hashtable<String,String>();
		
		values.put( SHIP_ID_KEY, String.valueOf( _shipIDMap.indexOf( ship.getName() ) ));
		values.put( "x", String.valueOf( ship.getLocation().x ) );
		values.put( "y", String.valueOf( ship.getLocation().y ) );
		values.put( "time", super.getDateTimeFormat().format( date ) );
		
		String status;
		if( ship.isMoving() ) { 
			status = "moving";
		} else {
			status = "stopped";
		}
		
		values.put( "status", status );
		values.put( "heading", String.valueOf( ship.getHeading() ) );
		
		if( _shipIDMap.indexOf( ship.getName()) == -1 ) {
			System.out.println( ship.getName());
			System.out.println( values.toString() );
		}
		super.put( LOCATION_TABLE, values);
		} catch( Exception e ) {
			e.printStackTrace();
		}
	}
}
